package com.typingtutor.dto;

import com.typingtutor.db.DatabaseManager;
import com.typingtutor.model.Folder;
import com.typingtutor.model.RandomRule;
import org.apache.commons.compress.utils.Lists;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class FolderDAO extends BaseDAO {
    private static volatile FolderDAO INSTANCE;
    private FolderDAO() {
        // 私有构造函数，防止实例化
    }
    public static FolderDAO getInstance() {
        if (INSTANCE == null) {
            synchronized (FolderDAO.class) {
                if (INSTANCE == null) {
                    INSTANCE = new FolderDAO();
                }
            }
        }
        return INSTANCE;
    }
    public String createFolder(String id, String name, String parentFolderId) {
        String sql = "INSERT INTO folders (id, name, parent_folder_id) VALUES (?, ?, ?)";
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, id);
            pstmt.setString(2, name);
            pstmt.setString(3, parentFolderId);
            pstmt.executeUpdate();
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                if (rs.next()) {
                    return rs.getString(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

    public String saveFolderConfiguration(Folder folder, List<RandomRule> randomRules) {
        String insertFolderRandomRulesSql = "INSERT INTO folder_random_rules (id, random_range, operator_str, " +
                "math_field, default_enabled, folder_id) VALUES (?,?,?,?,?,?)";
        String updateFolderRandomRulesSql = "UPDATE folder_random_rules SET random_range = ?, operator_str = " +
                "?, math_field = ?, default_enabled = ?, folder_id = ? WHERE id = ?";
        String selectFolderRandomRulesSql = "SELECT id FROM folder_random_rules WHERE id = ?";
        Connection connection = null;
        try {
            connection = DatabaseManager.getConnection();
            connection.setAutoCommit(false);

            for (RandomRule randomRule : randomRules) {
                // 先查询是否存在该 id 的记录
                PreparedStatement selectPstmt = connection.prepareStatement(selectFolderRandomRulesSql);
                selectPstmt.setString(1, randomRule.getId());
                ResultSet resultSet = selectPstmt.executeQuery();

                if (resultSet.next()) {
                    // 如果存在，则进行更新操作
                    PreparedStatement updatePstmt = connection.prepareStatement(updateFolderRandomRulesSql);
                    updatePstmt.setString(1, randomRule.getRandomRange());
                    updatePstmt.setString(2, randomRule.getOperatorStr());
                    updatePstmt.setString(3, randomRule.getMathField());
                    updatePstmt.setBoolean(4, randomRule.isDefaultEnabled());
                    updatePstmt.setString(5, folder.getId());
                    updatePstmt.setString(6, randomRule.getId());
                    updatePstmt.executeUpdate();
                    updatePstmt.close();
                } else {
                    // 如果不存在，则进行插入操作
                    PreparedStatement insertPstmt = connection.prepareStatement(insertFolderRandomRulesSql);
                    insertPstmt.setString(1, randomRule.getId());
                    insertPstmt.setString(2, randomRule.getRandomRange());
                    insertPstmt.setString(3, randomRule.getOperatorStr());
                    insertPstmt.setString(4, randomRule.getMathField());
                    insertPstmt.setBoolean(5, randomRule.isDefaultEnabled());
                    insertPstmt.setString(6, folder.getId());
                    insertPstmt.executeUpdate();
                    insertPstmt.close();
                }
                selectPstmt.close();
            }
            connection.commit();
        } catch (SQLException e) {
            // 回滚事务
            if (connection != null) {
                try {
                    connection.rollback();
                    System.out.println("事务回滚！");
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 关闭连接
            try {
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<RandomRule> findConfigurationById(String folderId) {
        String sql = "SELECT * FROM folder_random_rules WHERE folder_id = ? ORDER BY sort_order ASC";
        try (Connection conn = DatabaseManager.getConnection();) {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, folderId);
            ResultSet rs = pstmt.executeQuery();
            List<RandomRule> randomRules = Lists.newArrayList();
            while (rs.next()) {
                randomRules.add(new RandomRule(rs.getString("id"),
                        rs.getString("random_range"), rs.getString("operator_str")
                        , rs.getString("math_field"), rs.getBoolean("default_enabled")
                , rs.getString("folder_id")));
            }
            return randomRules;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    public List<Folder> getFoldersByParentId(String parentFolderId) {
        List<Folder> folders = new ArrayList<>();
        String sql = "";
        if (null == parentFolderId) {
            sql = "SELECT * FROM folders WHERE parent_folder_id IS NULL";
        } else {
            sql = "SELECT * FROM folders WHERE parent_folder_id = ?";
        }
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            if (null != parentFolderId) {
                pstmt.setObject(1, parentFolderId);
            }
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String parentId = rs.getString("parent_folder_id");
                    folders.add(new Folder(id, name, parentId));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return folders;
    }

    public boolean updateFolderName(String folderId, String newName) {
        String sql = "UPDATE folders SET name = ? WHERE id = ?";
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, newName);
            pstmt.setString(2, folderId);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean deleteFolder(String folderId) {
        try (Connection conn = DatabaseManager.getConnection()) {
            // 开启事务
            conn.setAutoCommit(false);

            try {
                // 递归删除子文件夹及其关联数据
                deleteSubFolders(folderId);

                // 删除该文件夹关联的随机规则
                deleteFolderRandomRules(conn, folderId);

                // 删除该文件夹下的所有文档及其关联的字段映射
                deleteDocumentsAndFieldMapping(conn, folderId);

                // 删除该文件夹本身
                String deleteFolderSql = "DELETE FROM folders WHERE id = ?";
                try (PreparedStatement pstmt = conn.prepareStatement(deleteFolderSql)) {
                    pstmt.setString(1, folderId);
                    pstmt.executeUpdate();
                }

                // 提交事务
                conn.commit();
            } catch (SQLException e) {
                // 回滚事务
                conn.rollback();
                e.printStackTrace();
            } finally {
                // 恢复自动提交模式
                conn.setAutoCommit(true);
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Folder> findSubFolderById(String id) {
        String selectSql = "SELECT * FROM folders WHERE parent_folder_id = ?";
        try (Connection conn = DatabaseManager.getConnection();) {
            PreparedStatement pstmt = conn.prepareStatement(selectSql);
            pstmt.setString(1, id);
            ResultSet rs = pstmt.executeQuery();
            List<Folder> folders = Lists.newArrayList();
            while (rs.next()) {
                folders.add(new Folder(rs.getString("id"), rs.getString("name"), rs.getString("parent_folder_id")));
            }
            return folders;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    private void deleteSubFolders(String parentFolderId) throws SQLException {
        // 查询该文件夹下的所有子文件夹
        String selectSubFoldersSql = "SELECT id FROM folders WHERE parent_folder_id = ?";
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(selectSubFoldersSql)) {
            pstmt.setString(1, parentFolderId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String subFolderId = rs.getString("id");
                // 递归删除子文件夹及其关联数据
                deleteSubFolders(subFolderId);

                // 删除子文件夹关联的随机规则
                deleteFolderRandomRules(conn, subFolderId);

                // 删除子文件夹下的所有文档及其关联的字段映射
                deleteDocumentsAndFieldMapping(conn, subFolderId);

                // 删除子文件夹本身
                String deleteSubFolderSql = "DELETE FROM folders WHERE id = ?";
                try (PreparedStatement deletePstmt = conn.prepareStatement(deleteSubFolderSql)) {
                    deletePstmt.setString(1, subFolderId);
                    deletePstmt.executeUpdate();
                }
            }
        }
    }

    private void deleteFolderRandomRules(Connection conn, String folderId) throws SQLException {
        String deleteRandomRulesSql = "DELETE FROM folder_random_rules WHERE folder_id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(deleteRandomRulesSql)) {
            pstmt.setString(1, folderId);
            pstmt.executeUpdate();
        }
    }

    public List<Folder> findAllSubFolderByIdAndFolderName(String folderId, String folderName) {
        String sql = "WITH RECURSIVE folder_tree(id, `name`, parent_folder_id) AS (" +
                "    SELECT id, `name`, parent_folder_id" +
                "    FROM folders" +
                "    WHERE parent_folder_id = ? AND `name` = ?" +
                "    UNION ALL" +
                "    SELECT f.id, f.`name`, f.parent_folder_id" +
                "    FROM folders f" +
                "    JOIN folder_tree ft ON f.parent_folder_id = ft.id" +
                ")" +
                "SELECT *" +
                "FROM folder_tree;";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, folderId);
            pstmt.setString(2, folderName);
            ResultSet rs = pstmt.executeQuery();
            List<Folder> folders = new ArrayList<>();
            while (rs.next()) {
                folders.add(new Folder(rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("parent_folder_id")));
            }
            return folders;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    private void deleteDocumentsAndFieldMapping(Connection conn, String folderId) throws SQLException {
        // 删除该文件夹下文档对应的字段映射数据
        String deleteFieldMappingSql = "DELETE FROM field_mapping WHERE document_id IN (SELECT id FROM documents WHERE folder_id = ?)";
        try (PreparedStatement pstmt1 = conn.prepareStatement(deleteFieldMappingSql)) {
            pstmt1.setString(1, folderId);
            pstmt1.executeUpdate();
        }

        // 删除该文件夹下的所有文档
        String deleteDocumentsSql = "DELETE FROM documents WHERE folder_id = ?";
        try (PreparedStatement pstmt2 = conn.prepareStatement(deleteDocumentsSql)) {
            pstmt2.setString(1, folderId);
            pstmt2.executeUpdate();
        }
    }
}